package com.brother.service;

import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import com.brother.database.DataBase;
import com.brother.model.Customer;
import com.brother.model.Tracker;
import com.brother.util.MD5;

public class UserService extends BaseService {
	private Logger log = Logger.getLogger(this.getClass());

	public Customer login(Customer c) {
		Customer match = null;
		log.debug("user=" + c.getName() + " login...");
		if (c != null && c.getName() != null) {
			//String sql = "select * from user where name='" + c.getName()
					String sql = "select * from user1 where name='" + c.getName()
					+ "' and password='" + this.getEncyptPass(c.getPassword())
					+ "'";

			try {
				ResultSet rs = DataBase.query(sql);
				if (rs.next()) {
					match = setUserResult(rs);
				}

			} catch (SQLException e) {
				Logger.getLogger(DataBase.class).error(e);
			} catch (Exception e) {
				Logger.getLogger(DataBase.class).error(e);
			}
			if (match != null) {
				sql = "update user1 set timeZoneOffSet=" + c.getTimeZoneOffSet()
						+ " where userid=" + match.getUserid();
				DataBase.update(sql);
				match.setTimeZoneOffSet(c.getTimeZoneOffSet());
			}
			DataBase.closeConnection();
		}
		return match;
	}

	public boolean setUserLocale(String locale, int userid) {
		String sql = "update user1 set locale='" + locale + "' where userid="
				+ userid;
		return DataBase.update(sql) > 0;
	}

	public List<Customer> getAllAdmins() {

		List<Customer> r = new ArrayList();

		String sql = "select * from user1 where  isAdmin=1";

		try {
			ResultSet rs = DataBase.query(sql);
			while (rs.next()) {
				Customer c = setUserResult(rs);
				r.add(c);
			}

		} catch (SQLException e) {
			Logger.getLogger(DataBase.class).error(e);
		} catch (Exception e) {
			Logger.getLogger(DataBase.class).error(e);
		}

		DataBase.closeConnection();

		return r;
	}

	public List<Customer> getChildUsers(int userid, String name) {
		List<Customer> r = new ArrayList();
		// 1 parent, 2parent,3parent
		String sql = "select * from user1 where  (parentid='" + userid
				+ "' or parentid like '" + userid + ",%' or parentid like '%,"
				+ userid + "' or parentid like '%," + userid + ",%')";

		if (name != null && !name.trim().isEmpty()) {
			sql = sql + " and name like '" + name + "%'";
		}
		
		sql = sql + " order by name ";
		//sql = sql + " limit 100 ";
		try {
			ResultSet rs = DataBase.query(sql);
			while (rs.next()) {
				Customer c = setUserResult(rs);
				r.add(c);
			}

		} catch (SQLException e) {
			Logger.getLogger(DataBase.class).error(e);
		} catch (Exception e) {
			Logger.getLogger(DataBase.class).error(e);
		}

		DataBase.closeConnection();

		return r;
	}

	public List<Customer> getPageUsers(int pageNo, int pageSize, int userid) {
		List<Customer> all = this.getChildUsers(userid, null);

		int fromIndex = pageNo * pageSize;
		int toIndex = pageNo * pageSize + pageSize;

		if (fromIndex > all.size()) {
			fromIndex = all.size();
		}

		if (toIndex > all.size()) {
			toIndex = all.size();
		}

		return all.subList(fromIndex, toIndex);

	}

	public int getChildUsersCount(Customer user) {
		int result = 0;

		String sql = "select count(*) from user1 where  parentid='"
				+ user.getUserid() + "' or parentid like '" + user.getUserid()
				+ ",%' or parentid like '%," + user.getUserid()
				+ "' or parentid like '%," + user.getUserid() + ",%'";

		try {
			ResultSet rs = DataBase.query(sql);
			if (rs.next()) {
				result = rs.getInt(1);
			}
			DataBase.closeConnection();
		} catch (SQLException e) {
			Logger.getLogger(DataBase.class).error(e);
		} catch (Exception e) {
			Logger.getLogger(DataBase.class).error(e);
		}

		return result;
	}

	/**
	 * Detele its child user.
	 * 
	 * @param op
	 * @param userid
	 * @return
	 */
	public int deleteUser(Customer op, int userid) {

		String sql = "delete from user1 where  1=1 ";

		if (!StringUtils.isEmpty(op.getParentid())) {
			sql = sql + " and (parentid='" + op.getUserid()
					+ "' or parentid like '" + op.getUserid()
					+ ",%' or parentid like '%," + op.getUserid()
					+ "' or parentid like '%," + op.getUserid() + ",%') ";
		}
		sql = sql + " and userid=" + userid;

		if (log.isDebugEnabled()) {
			log.debug(sql);
		}
		List<Customer> children = this.getChildUsers(userid, null);

		for (Customer c : children) {
			TrackerService ts = new TrackerService();
			List<Tracker> trackers = ts.findUserTracker(c.getUserid());
			for (Tracker tracker : trackers) {
				ts.deleteTracker(tracker.getTrackerid());
			}
		}
		TrackerService ts = new TrackerService();
		List<Tracker> trackers = ts.findUserTracker(userid);
		for (Tracker tracker : trackers) {
			ts.deleteTracker(tracker.getTrackerid());
		}

		int r = 0;
		r = DataBase.update(sql);

		DataBase.closeConnection();

		return r;
	}

	public Customer getSuperiorUser(String userid) {
		//String sql = "select * from user1 where userid=(select SUBSTRING_INDEX(parentid,',',-1) from user1 where userid="
				String sql = "select * from user1 where userid=(select substr(parentid,(instr(parentid,',',-1,1)+1), ((LENGTH(parentid))-(instr(parentid,',',-1,1))+1)) from user1 where userid="
				+ userid + ")";
		Customer user = null;

		try {
			ResultSet rs = DataBase.query(sql);
			if (rs.next()) {
				user = setUserResult(rs);
			}

		} catch (SQLException e) {
			Logger.getLogger(DataBase.class).error(e);
		} catch (Exception e) {
			Logger.getLogger(DataBase.class).error(e);
		}

		DataBase.closeConnection();

		return user;
	}

	public void addUser(Customer c) {

		String sql = "insert into user1(name,password,email,description,isAdmin, parentid) values('"
				+ c.getName()
				+ "',"
				+ "'"
				+ this.getEncyptPass(c.getPassword())
				+ "','"
				+ c.getEmail()
				+ "','"
				+ c.getDescription()
				+ "',"
				+ (c.isAdmin() ? 1 : 0)
				+ "," + c.getParentid() + ")";
		if (log.isDebugEnabled())
			log.debug(sql);
		DataBase.update(sql);

		DataBase.closeConnection();
	}

	public void updateUserParent(Customer c, String oldParentId) {

		List<Customer> children = this.getChildUsers(c.getUserid(), null);

		String sql = "update user1 set parentid=" + c.getParentid()
				+ " where userid=" + c.getUserid();
		DataBase.update(sql);

		for (Customer child : children) {
			child.setParentid(child.getParentid().replace(oldParentId + ",",
					c.getParentid() == null ? "" : (c.getParentid() + ",")));
			sql = "update user1 set parentid=" + child.getParentid()
					+ " where userid=" + child.getUserid();
			DataBase.update(sql);
		}

		DataBase.closeConnection();
	}

	public void updateUser(Customer c) {
		List<Customer> children = this.getChildUsers(c.getUserid(), null);

		String sql = "update user1 set parentid='" + c.getParentid() + "',"
				+ "password='" + MD5.getMD(c.getPassword()) + "',isAdmin="
				+ (c.isAdmin() ? 1 : 0) + ",email='" + c.getEmail()
				+ "',description='" + c.getDescription() + "' where userid="
				+ c.getUserid();

		if (log.isDebugEnabled()) {
			log.debug(sql);
		}
		DataBase.update(sql);

		DataBase.closeConnection();
	}

	public boolean normalUpdateUser(Customer c) {

		escapeAllFields(c);

		String sql = "update user1 set name='" + c.getName() + "',";
		if (c.getPassword() != null) {
			sql = sql + "password='" + MD5.getMD(c.getPassword()) + "',";
		}

		sql = sql + "email='" + c.getEmail() + "',description='"
				+ c.getDescription() + "'" + ",realname='" + c.getRealName()
				+ "',gender='" + c.getGender() + "',companyName='"
				+ c.getCompanyName() + "'," + "address='" + c.getAddress()
				+ "',country='" + c.getCountry() + "',mobile='" + c.getMobile()
				+ "',fax='" + c.getFax() + "',phoneNumber='"
				+ c.getPhoneNumber() + "',isAdmin=" + (c.isAdmin() ? 1 : 0)
				+ " where userid=" + c.getUserid();

		sql = sql.replaceAll("'null'", "''");
		if (log.isDebugEnabled()) {
			log.debug(sql);
		}

		DataBase.update(sql);

		DataBase.closeConnection();

		return false;
	}

	public void normalAddUser(Customer c) {
		escapeAllFields(c);
		String sql = "insert into user1(name,password,email,description,isAdmin, parentid,"
				+ "realName,gender,companyName,address,country,mobile,fax,phoneNumber)"
				+ " values('"
				+ c.getName()
				+ "',"
				+ "'"
				+ this.getEncyptPass(c.getPassword())
				+ "','"
				+ c.getEmail()
				+ "','"
				+ c.getDescription()
				+ "',"
				+ (c.isAdmin() ? 1 : 0)
				+ ",'"
				+ c.getParentid()
				+ "','"
				+ c.getRealName()
				+ "','"
				+ c.getGender()
				+ "','"
				+ c.getCompanyName()
				+ "','"
				+ c.getAddress()
				+ "','"
				+ c.getCountry()
				+ "','"
				+ c.getMobile()
				+ "','"
				+ c.getFax()
				+ "','"
				+ c.getPhoneNumber() + "')";

		sql = sql.replaceAll("'null'", "''");

		if (log.isDebugEnabled())
			log.debug(sql);
		DataBase.update(sql);

		DataBase.closeConnection();
	}

	public int modifyPassword(String newpass, String userid, String oldPass) {

		// 123456 for -3110-365773-7089-85-6686-3287-1415-12062
		String oldEnPass = this.getEncyptPass(oldPass);
		String enPa = this.getEncyptPass(newpass);
		String sql = "update user1 set password='" + enPa + "' where password='"
				+ oldEnPass + "' and userid='" + userid + "'";
		int r = DataBase.update(sql);
		DataBase.closeConnection();
		return r;

	}

	public String getEncyptPass(String pass) {
		if (pass == null) {
			return null;
		}
		MessageDigest d = null;
		;
		try {
			d = MessageDigest.getInstance("MD5");
		} catch (NoSuchAlgorithmException e) {
			Logger.getLogger(this.getClass()).error(e);
		}
		d.update(pass.getBytes());
		byte[] pa = d.digest();
		String enPass = "";
		for (int i = 0; i < pa.length; i++) {
			enPass += pa[i];
		}
		return enPass;
	}

	public Customer GetUserByUserId(int userid) {
		String sql = "select * from user1 where userid=" + userid;
		Customer c = null;

		try {
			ResultSet rs = DataBase.query(sql);
			if (rs.next()) {
				c = setUserResult(rs);
			}

		} catch (SQLException e) {
			Logger.getLogger(DataBase.class).error(e);
		} catch (Exception e) {
			Logger.getLogger(DataBase.class).error(e);
		} finally {
			DataBase.closeConnection();
		}
		return c;
	}

	public boolean UpdateUserListPwd() {

		String sql = "select name,password from userinfolist$";
		try {
			ResultSet rs = DataBase.query(sql);
			while (rs.next()) {

				String name = rs.getString("name");
				String pwd = rs.getString("password");
				String sql2 = "update userinfolist$ set password='"+ getEncyptPass(pwd)+"' where name='" + name + "'";
				DataBase.update(sql2);

			}
			DataBase.closeConnection();
		} catch (Exception e) {
			Logger.getLogger(DataBase.class).error(e);
		}
		return false;
	}

	public Customer GetUserByUserFieldName(String fieldName, String value) {
		String sql = "select * from user1 where " + fieldName + "='" + value
				+ "'";
		Customer c = null;

		try {
			ResultSet rs = DataBase.query(sql);
			if (rs.next()) {
				c = setUserResult(rs);
			}

		} catch (SQLException e) {
			Logger.getLogger(DataBase.class).error(e);
		} catch (Exception e) {
			Logger.getLogger(DataBase.class).error(e);
		} finally {
			DataBase.closeConnection();
		}

		return c;
	}

	public Customer setUserResult(ResultSet rs) throws SQLException {
		Customer match = new Customer();
		match.setEmail(rs.getString("email"));
		match.setName(rs.getString("name"));
		match.setPassword(rs.getString("password"));
		match.setAdmin(rs.getBoolean("isAdmin"));
		match.setUserid(rs.getInt("userid"));
		match.setTimeZoneOffSet("8"); // default is east 8 zone
		match.setParentid(rs.getString("parentid"));
		match.setAddress(rs.getString("address"));
		match.setGender(rs.getString("gender"));
		match.setCompanyName(rs.getString("companyName"));
		match.setCountry(rs.getString("country"));
		match.setDescription(rs.getString("description"));
		match.setFax(rs.getString("fax"));
		match.setMobile(rs.getString("mobile"));
		match.setRealName(rs.getString("realName"));
		match.setPhoneNumber(rs.getString("phoneNumber"));
		match.setLocale(rs.getString("locale"));

		return match;
	}

}
